﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Hotnet.Entity;
using Hotnet.Dal.BaseRepository;
using Component.Utilities;
using Hotnet.Entity.Enum;
using Hotnet.Entity.Search;

namespace Hotnet.Dal.NSDalNews
{
	public class DalNews : Repository<News>
	{
		public News[] ListByCategoryId(int categoryID, int pageSize, int pageNo, out int totalCount)
		{
			totalCount = (from news in disableCacheDataContext.News
						  where news.CategoryID == categoryID && news.IsPublished==1
						  select news).Count();

			var newses = (from news in disableCacheDataContext.News
						  where news.CategoryID == categoryID && news.IsPublished==1
						  orderby news.NewsTime descending
						  orderby news.ClickCount
						  select news).Skip(pageSize * pageNo).Take(pageSize);
			return newses.ToArray();
		}

		public News[] ListByCategoryId(SearchNewsCondition condition, int categoryID, int pageSize, int pageNo, out int totalCount,int? isPublished)
		{
			string newsOrderByCondtion = "";
			string newsNameCondition = "";
			string newsSourceFromCondition = "";
			string newsDefineTypeCondition = "";
			string newsCategoryCondition = "";
			string isPublishedCondition = "";
			if (isPublished != null && isPublished != (int)EnumPublishState.全部)
				isPublishedCondition = string.Format(" and ispublished={0} ", isPublished.Value);

			int from = pageNo * pageSize + 1;
			int to = (pageNo + 1) * pageSize;
			totalCount = 0;
			if (condition.CategoryID != null)
				newsCategoryCondition=string.Format(" and categoryId={0}", condition.CategoryID);
			if (condition.DefineType != null && condition.DefineType!=-1)
				newsDefineTypeCondition=string.Format(" and DefineType={0}", condition.DefineType);
			//if (condition.OrderType != null)
			//    sql.AppendFormat("");
			if (!string.IsNullOrEmpty(condition.NewsName))
				newsNameCondition=string.Format(" and title like '%{0}%'", condition.NewsName);
			if (condition.SourceFromID != null&&condition.SourceFromID!=-1)
				newsSourceFromCondition=string.Format(" and sourcefromid={0}", condition.SourceFromID);
			if (condition.OrderType == null || condition.OrderType == (int)EnumOrder.添加时间)
				newsOrderByCondtion = " NewsTime ";
			else
				newsOrderByCondtion = " ClickCount ";

			string strSql = string.Format(@"select * from (SELECT ROW_NUMBER() OVER (ORDER BY {4} desc)AS Row
										,[NewsID]
										,[Title]
										,[NewsTime]
										,[CategoryID]
										,[SourceFromID]
										,[ClickCount]
										,[DefineType]
										,[ImagePath]
										,[Summary]
										,[IsPublished]
										FROM [News] where 1=1 {0} {1} {2} {3} {7}) t1 WHERE Row between {5} and {6}", newsNameCondition, newsSourceFromCondition, newsDefineTypeCondition, newsCategoryCondition, newsOrderByCondtion,from,to,isPublishedCondition);
			string strCount = string.Format(@"select count(*)
									FROM [News] where 1=1 {0} {1} {2} {3} {4} ", newsNameCondition, newsSourceFromCondition, newsDefineTypeCondition, newsCategoryCondition,isPublishedCondition);

			totalCount = disableCacheDataContext.ExecuteQuery<int>(strCount).FirstOrDefault();
			return disableCacheDataContext.ExecuteQuery<News>(strSql).ToArray();
		}

		public News[] ListByCategoryId(int categoryID,EnumOrder order, int pageSize, int pageNo, out int totalCount)
		{
			totalCount = (from news in disableCacheDataContext.News
						  where news.CategoryID == categoryID
						  select news).Count();

			if (order == EnumOrder.点击次数)
				return (from news in disableCacheDataContext.News
						where news.CategoryID == categoryID && news.IsPublished==1
						orderby news.ClickCount descending
						select news).Skip(pageSize * pageNo).Take(pageSize).ToArray();
			else
				return (from news in disableCacheDataContext.News
						where news.CategoryID == categoryID && news.IsPublished==1
						orderby news.NewsTime descending
						select news).Skip(pageSize * pageNo).Take(pageSize).ToArray();
		}

		public News[] ListByDefineType(EnumDefineType defineType,EnumOrder order, int pageSize, int pageNo, out int totalCount)
		{
			totalCount = (from news in disableCacheDataContext.News
						  where news.DefineType == (int)defineType && news.IsPublished==1
						  select news).Count();
			if(order== EnumOrder.点击次数)
			return (from news in disableCacheDataContext.News
						  where news.DefineType == (int)defineType && news.IsPublished==1
						  orderby news.ClickCount descending
						  select news).Skip(pageSize * pageNo).Take(pageSize).ToArray();
			else
			return (from news in disableCacheDataContext.News
							   where news.DefineType == (int)defineType && news.IsPublished==1
							   orderby news.NewsTime descending
							   select news).Skip(pageSize * pageNo).Take(pageSize).ToArray();
		}



		/// <summary>
		/// 根据tagName 列出相关新闻,除去newsId的新闻
		/// </summary>
		/// <param name="tagIdList"></param>
		/// <param name="newsId"></param>
		/// <param name="count"></param>
		/// <returns></returns>
		public News[] ListReferenceNewsByNewsID(IList<string> tagNameList, int newsId, int count)
		{
			if (tagNameList == null || tagNameList.Count <= 0)
				return new News[] { };
			var query = (from news in disableCacheDataContext.News
					join nxt in disableCacheDataContext.NewsXTag on news.NewsID equals nxt.NewsID
					join tag in disableCacheDataContext.Tag on nxt.TagID equals tag.TagID
					where tagNameList.ToArray().Contains(tag.TagName) && news.NewsID != newsId && tag.TargetType == (int)EnumTagType.新闻 && news.IsPublished==1
					orderby news.ClickCount descending
					select news).Distinct().Take(count);
			var newses = query.ToArray();
			return newses;
		}

		/// <summary>
		/// 按照tagName 列出新闻
		/// </summary>
		/// <param name="tagNameList">tag名称列表</param>
		/// <param name="pageSize"></param>
		/// <param name="pageNo"></param>
		/// <param name="totalCount"></param>
		/// <returns></returns>
		public News[] ListNewsByTag(IList<string> tagNameList,int pageSize,int pageNo,out int totalCount)
		{
			totalCount = 0;
			if (tagNameList == null || tagNameList.Count <= 0)
				return new News[] { };
			totalCount = (from news in disableCacheDataContext.News
						  join nxt in disableCacheDataContext.NewsXTag on news.NewsID equals nxt.NewsID
						  join tag in disableCacheDataContext.Tag on nxt.TagID equals tag.TagID
						  where tagNameList.ToArray().Contains(tag.TagName) && tag.TargetType == (int)EnumTagType.新闻 && news.IsPublished==1
						  orderby news.ClickCount descending
						  select news.NewsID).Distinct().Count();

			var query = (from news in disableCacheDataContext.News
						 join nxt in disableCacheDataContext.NewsXTag on news.NewsID equals nxt.NewsID
						 join tag in disableCacheDataContext.Tag on nxt.TagID equals tag.TagID
						 where tagNameList.ToArray().Contains(tag.TagName) && tag.TargetType == (int)EnumTagType.新闻 && news.IsPublished==1
						 orderby news.ClickCount descending
						 select news).Distinct().Skip(pageSize*pageNo).Take(pageSize);
			return query.ToArray();
		}

		/// <summary>
		/// 数量不足,随机补充新闻
		/// </summary>
		/// <param name="news"></param>
		/// <param name="number"></param>
		/// <returns></returns>
		public News[] NewsSupplyWhenNotEnough(News[] news, int number)
		{
			if (news.Count() - number < 0)
			{
				News[] extraGroup = (from n in disableCacheDataContext.News where !news.Select(x => x.NewsID).Contains(n.NewsID) && n.IsPublished==1 select n).OrderBy(x => disableCacheDataContext.GetNewId()).Take(number-news.Count()).ToArray();
				news = news.Union(extraGroup).ToArray();
			}
			return news;
		}

		/// <summary>
		/// 根据tag 列出相关新闻
		/// </summary>
		/// <param name="tagIdList"></param>
		/// <param name="count"></param>
		/// <returns></returns>
		public News[] ListReferenceNews(IList<string> tagNameList, int count)
		{
			if (tagNameList == null || tagNameList.Count <= 0)
				return new News[] { };
			var query = (from news in disableCacheDataContext.News
						 join nxt in disableCacheDataContext.NewsXTag on
							 news.NewsID equals nxt.NewsID
						 join tag in disableCacheDataContext.Tag on
							 nxt.TagID equals tag.TagID
						 where tagNameList.ToArray().Contains(tag.TagName) && tag.TargetType == (int)EnumTagType.新闻 && news.IsPublished==1
						 orderby news.ClickCount descending
						 select news).Distinct().Take(count);
			var newses = query.ToArray();
			return newses;
		}

		/// <summary>
		/// 列出每个新闻分类的头条大眼睛，数量为1个
		/// </summary>
		/// <param name="order"></param>
		/// <returns></returns>
		public News[] ListEveryCategoryTopEye()
		{
			string strSql = string.Format(@"select t2.* from newsCategory t1
										cross apply
										(
											select top 1 * from news where news.categoryID=t1.CategoryID and DefineType={0} and news.IsPublished=1 order by NewsTime desc
										) t2 order by t2.CategoryID",(int)EnumDefineType.分类轮换);
			return disableCacheDataContext.ExecuteQuery<News>(strSql).ToArray();
		}

		/// <summary>
		/// 列出最新x条新闻
		/// </summary>
		/// <param name="number"></param>
		/// <returns></returns>
		public News[] ListTopXLatestNews(int number)
		{
			return diableCacheGiveTable.Where(x => (x.DefineType == null||x.DefineType==-1) && x.IsPublished==1).OrderByDescending(x => x.NewsTime).Take(number).ToArray();
		}

		/// <summary>
		/// 列出某一个分类最新的新闻
		/// </summary>
		/// <param name="categoryId"></param>
		/// <param name="number"></param>
		/// <returns></returns>
		public News[] ListCategoryTopXLatestNews(int categoryId,int number)
		{
			return diableCacheGiveTable.Where(x => (x.DefineType == null||x.DefineType==-1) && x.CategoryID == categoryId && x.IsPublished==1).OrderByDescending(x => x.NewsTime).Take(number).ToArray();
		}



		/// <summary>
		/// 按照defineType 获取列表
		/// </summary>
		/// <param name="number"></param>
		/// <param name="defineType"></param>
		/// <param name="order"></param>
		/// <returns></returns>
		public News[] ListTopXByDefineType(int number,EnumDefineType defineType,EnumOrder order)
		{
			IQueryable<News> news = diableCacheGiveTable.Where(x => x.DefineType == (int)defineType&& x.IsPublished==1);
			news = order == EnumOrder.点击次数 ? news.OrderByDescending(x => x.ClickCount) : news.OrderByDescending(x=>x.NewsTime);
			news = news.Take(number);
			return news.ToArray();
		}

		/// <summary>
		/// 按照defineType 获取列表
		/// </summary>
		/// <param name="number"></param>
		/// <param name="defineType"></param>
		/// <param name="order"></param>
		/// <returns></returns>
		public News[] ListCategoryTopXByDefineType(int number,int categoryId, EnumDefineType defineType, EnumOrder order)
		{
			IQueryable<News> news = diableCacheGiveTable.Where(x => x.DefineType == (int)defineType&&x.CategoryID==categoryId && x.IsPublished==1);
			news = order == EnumOrder.点击次数 ? news.OrderByDescending(x => x.ClickCount) : news.OrderByDescending(x => x.NewsTime);
			news = news.Take(number);
			return news.ToArray();
		}

		/// <summary>
		/// 列出news分类点击靠前的视频,
		/// </summary>
		/// <param name="topNumber">top 数</param>
		/// <returns></returns>
		public News[] ListTopXNewsByCategory(int topNumber)
		{
			string strSql = string.Format(@"select t2.* from NewsCategory t1
										cross apply
										(
											select top {0} * from news where news.categoryID=t1.categoryID and news.IsPublished=1 order by clickCount desc
										) t2", topNumber);
			return disableCacheDataContext.ExecuteQuery<News>(strSql).ToArray();
		}

		/// <summary>
		/// 列出topX 点击次数最多的新闻
		/// </summary>
		/// <param name="number"></param>
		/// <returns></returns>
		public News[] ListTopXHotNews(int number)
		{
			return diableCacheGiveTable.Where(x=>(x.DefineType==null||x.DefineType==-1)&& x.IsPublished==1).OrderByDescending(x => x.ClickCount).Take(number).ToArray();
		}

		/// <summary>
		/// 列出某一分类下面的热门新闻
		/// </summary>
		/// <param name="categoryId"></param>
		/// <param name="number"></param>
		/// <returns></returns>
		public News[] ListCategoryTopXHotNews(int categoryId, int number)
		{
			return diableCacheGiveTable.Where(x => (x.DefineType == null||x.DefineType==-1)&&x.CategoryID==categoryId && x.IsPublished==1).OrderByDescending(x => x.ClickCount).Take(number).ToArray();
		}

	}
}
